package com.pwt.repository;

import com.pwt.model.EventMeasuresModel;
import com.pwt.repository.base.BaseRepository;
import com.pwt.entity.WERTEVAR;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * WERTEVAR Repository层
 *
 * @author auto generated
 * @date 2021-09-23 17:53:27
 */
public interface WERTEVARRepository extends BaseRepository<WERTEVAR, Integer>,WERTEVARRepositoryExtension {

    @Query(nativeQuery = true,value = "select  top 1 * from WERTEVAR  order by WVDATZEIT ")
    WERTEVAR queryWERTEVAROrderBy();

    @Query(nativeQuery = true,value = "SELECT ISNULL(COUNT( 1 ),0) FROM ( SELECT WV0014 FROM WERTEVAR WHERE DATEDIFF( DAY, WVDATZEIT, GETDATE( ) ) = ? GROUP BY WV0014 ) a")
    Integer getTotalData(Integer day);

    @Query(nativeQuery = true,value = "select distinct WV0014 FROM WERTEVAR WHERE DATEDIFF( DAY, WVDATZEIT, GETDATE( ) ) = ?1 ")
    List<String> queryWV0014ByDay(Integer day);

    @Query(nativeQuery = true,value = "select distinct WV0014 FROM WERTEVAR WHERE DATEDIFF( month, WVDATZEIT, ?1 ) = 0  ")
    List<String> queryWV0014ByMonth(String month);

    @Query(nativeQuery = true,value = "select distinct WV0014 FROM WERTEVAR WHERE DATEDIFF( DAY, WVDATZEIT, ?1 ) = 0  ")
    List<String> queryWV0014ByDay(String day);

    //计算sigma
    @Query(nativeQuery = true,value = "select top 125 WVWERT FROM WERTEVAR WHERE  WVTEIL=?1 and WVMERkMAL=?2 and WV0014 =?3")
    List<Double> queryWERTEVARByDay(Integer k1000,Short k2000,String k0014);

    @Query(nativeQuery = true,value = "select top 125 WVWERT FROM WERTEVAR WHERE DATEDIFF( month, WVDATZEIT, ?1 ) = 0 and  WVTEIL=?2 and WVMERkMAL=?3 and WV0014 =?4")
    List<Double> queryWERTEVARByMonth(String month,Integer k1000,Short k2000,String k0014);

    @Query(nativeQuery = true,value = "select top 125 WVWERT FROM WERTEVAR WHERE DATEDIFF( DAY, WVDATZEIT, ?1 ) = 0 and  WVTEIL=?2 and WVMERkMAL=?3 and WV0014 =?4")
    List<Double> queryWERTEVARByDay(String day,Integer k1000,Short k2000,String k0014);

    @Query(nativeQuery = true,value = "SELECT MAX(WVDATZEIT) from WERTEVAR where DATEDIFF(hh, CREATETIME, GETDATE())<=2")
    Date queryMaxWVDATZEIT();

    @Query(nativeQuery = true,value = "SELECT MIN(WVDATZEIT) from WERTEVAR where DATEDIFF(hh, CREATETIME, GETDATE())<=2")
    Date queryMinWVDATZEIT();

    @Query(nativeQuery = true,value = "SELECT WVWERT from WERTEVAR where WVTEIL=?1  and WVMERKMAL=?2  and WVDATZEIT>=?3 and WVDATZEIT<=?4 ")
    List<Double> queryWERTEVARByWVDATZEIT(Integer k1000,Short k2000,String beginTime,String endTime);

    @Query(nativeQuery = true,value = "select  * from WERTEVAR where  DATEDIFF(ss,?1,CREATETIME)>=0 and DATEDIFF(ss,?1,CREATETIME) <=1")
    List<WERTEVAR> queryAllByWVDATZEIT(Date beginTime);

    @Query(nativeQuery = true,value = "select max(WVDATZEIT) from WERTEVAR")
    Date queryNewestWVDATZEIT();

    @Query(nativeQuery = true,value = "select top 1 ISNULL(WVWERT, 0.00) from WERTEVAR where WVTEIL=?1 and WVMERKMAL=?2 and WVWERTNR<?3 order by WVWERTNR desc ")
    Double findByWVTEILAndWVMERkMALAndWVWERTNR(Integer teil,Short merkmal,Integer wertnr);
}
